package controleur;

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;     
import java.io.IOException;
import java.sql.ResultSet;
import java.util.ArrayList;   
import java.util.List;

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import persistence.Bdd;


/**
 *
 * @author Nishruu
 */
@WebServlet(name="NewServlet", urlPatterns={"/NewServlet"})
public class MainServlet extends HttpServlet {

    private List<String[]> listeSemaines;
    private List<String[]> listeVilles;
    private List<String[]> listeRegions;
    private String redirect = "";
   
    
    public MainServlet() {
        System.setProperty("http.maxConnections","16");
        try {
            listeSemaines = getSemaines();
            listeVilles = getVilles();
            listeRegions = getRegions();
        }
        catch (SQLException ex) {
            Logger.getLogger(MainServlet.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    

    protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

        Bdd bd = new Bdd();

        response.setContentType("text/html;charset=UTF-8");
        String action = request.getParameter("action");

        try {
        
        if(action.equals("map")){
            ResultSet rs = bd.select("select max(debut_semaine) from semaine;");
            rs.next();
            int semaine = rs.getInt(1);
            rs.close();
            request.setAttribute("semaine", semaine);
            request.setAttribute("listeRegions",listeRegions);
            List<String> listeGroupes = new ArrayList<String>();
            String sql = "SELECT nom_groupe FROM groupe";
            rs = bd.select(sql);
            while(rs.next()) listeGroupes.add(rs.getString(1));
            request.setAttribute("listeGroupes",listeGroupes);
            request.setAttribute("groupe",request.getParameter("groupe"));
            redirect = "map.jsp";
        }else if(action.equals("searchGroupe")){
            List<String> liste = new ArrayList<String>();
            ResultSet rs = bd.select("select nom_groupe from groupe where nom_groupe like ?;", new String[]{request.getParameter("q")+"%"});
            while(rs.next()) liste.add(rs.getString(1));
            request.setAttribute("liste",liste);
            redirect = "searchGroupe.jsp";
        }else if(action.equals("getPopByCity")){
            String groupe = request.getParameter("groupe");
            ResultSet rs = bd.select("select id_groupe from groupe where nom_groupe = ?;", new String[]{groupe});
            if(!rs.next()) {response.sendError(404);return;}
            int idGroupe = rs.getInt(1);
            int semaine = Integer.parseInt(request.getParameter("semaine"));
            List<Object[]> liste = new ArrayList<Object[]>();
            String sql = "select v.latitude, v.longitude, p.popularite, v.nom_ville, v.id_ville from popularite_raw p inner join ville v on p.id_ville=v.id_ville inner join groupe g on p.id_groupe=g.id_groupe "
                       + "where p.id_semaine=? and g.id_groupe=?;";
            rs = bd.select(sql, new Object[] {semaine, idGroupe});
            while(rs.next()) {
                liste.add(new Object[] {rs.getDouble(1),rs.getDouble(2),rs.getInt(3),rs.getString(4),rs.getInt(5)});
            }
            rs.close();
            request.setAttribute("liste", liste);
            redirect = "popByCityJSON.jsp";
        }else if(action.equals("stats")){
            int idVille = Integer.parseInt(request.getParameter("idVille"));
            ResultSet rs = bd.select("select nom_ville from ville where id_ville = ?;", new Object[]{idVille});
            if(!rs.next()) {response.sendError(404);return;}
            String ville = rs.getString(1);
            request.setAttribute("groupe", request.getParameter("groupe"));
            request.setAttribute("ville", ville);
            request.setAttribute("idVille", idVille );
            redirect = "stats.jsp";
        }else if(action.equals("getStatsByCity")){
            int idVille = Integer.parseInt(request.getParameter("idVille"));
            String groupe = request.getParameter("groupe");
            if(groupe.isEmpty()) {response.sendError(400);return;}
            ResultSet rs = bd.select("select id_groupe from groupe where nom_groupe = ?;", new Object[]{groupe});
            if(!rs.next()) {response.sendError(404);return;}
            int idGroupe = rs.getInt(1);

            List<Object[]> liste = new ArrayList<Object[]>();
            String sql = "SELECT id_semaine, popularite FROM popularite_raw where id_groupe=? and id_ville=?;";
            rs = bd.select(sql, new Object[]{idGroupe, idVille});
            while(rs.next()) liste.add(new Object[]{rs.getInt("id_semaine"), rs.getInt("popularite")});
            request.setAttribute("liste",liste);
            redirect = "statsByCityJSON.jsp";
        }

       }
       catch (SQLException ex) {
            Logger.getLogger(MainServlet.class.getName()).log(Level.SEVERE, null, ex);
       }


        bd.close();
        RequestDispatcher dispatch = request.getRequestDispatcher(redirect);
        dispatch.forward(request, response);
    }

   

    private List<String[]> getVilles() throws SQLException {
        
        List<String[]> listevilles = new ArrayList<String[]>(2);
        Bdd bd = new Bdd();
        
        ResultSet rs = bd.select("SELECT nom_ville,nom_pays FROM VILLE");
        while (rs.next()) {
            String[] paire = new String[2];
            paire[0] = rs.getString("nom_ville");
            paire[1] = rs.getString("nom_pays");
            listevilles.add(paire);
        }

        bd.close();
        return listevilles;
    }

    private List<String[]> getSemaines() throws SQLException {

        List<String[]> listesemaines = new ArrayList<String[]>(2);
        Bdd bd = new Bdd();
         
        String sql = "SELECT debut_semaine,fin_semaine FROM SEMAINE";
        ResultSet rs = bd.select(sql);
        while (rs.next()) {
            String[] paire = new String[2];
            paire[0] = rs.getString("debut_semaine");
            paire[1] = rs.getString("fin_semaine");
            listesemaines.add(paire);
        }
   
        bd.close();
        return listesemaines;
    }

    private List<String[]> getRegions() throws SQLException {

        List<String[]> listeregions = new ArrayList<String[]>(2);
        Bdd bd = new Bdd();

        String sql = "SELECT code_pays, libelle_fr FROM REGION";
        ResultSet rs = bd.select(sql);
        while (rs.next()) {
            String[] paire = new String[2];
            paire[0] = rs.getString("code_pays");
            paire[1] = rs.getString("libelle_fr");
            listeregions.add(paire);
        }

        bd.close();
        return listeregions;
    }


    // <editor-fold defaultstate="collapsed" desc="HttpServlet methods. Click on the + sign on the left to edit the code.">
    /**
     * Handles the HTTP <code>GET</code> method.
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException {
        processRequest(request, response);
    }

    /**
     * Handles the HTTP <code>POST</code> method.
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException {
        processRequest(request, response);
    }

    /**
     * Returns a short description of the servlet.
     * @return a String containing servlet description
     */
    @Override
    public String getServletInfo() {
        return "Short description";
    }// </editor-fold>

}
